/*
 * ProductGroupDAO.java
 *
 * $Id: ProductGroupDAO.java,v 1.2 2009-03-02 15:31:23 mario Exp $
 */
package org.ceteca.explica.server.warehouse.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.ceteca.explica.core.ExplicaException;
import org.ceteca.explica.core.InterfaceErrors;
import org.ceteca.explica.core.util.OperationResult;
import org.ceteca.explica.core.util.ResultMessage;
import org.ceteca.explica.core.warehouse.InterfaceWarehouse;
import org.ceteca.explica.core.warehouse.InterfaceWarehouseErrors;
import org.ceteca.explica.core.warehouse.ProductGroupOT;
import org.ceteca.explica.core.warehouse.ProductOT;
import org.ceteca.explica.server.ServerContext;
import org.ceteca.explica.server.util.db.CriteriaBean;
import org.ceteca.explica.server.util.db.HibernateUtil;
import org.ceteca.explica.server.util.db.InterfaceDB;
import org.ceteca.explica.server.util.db.SQLGenerator;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 * This class implements the product group data access object. It's 
 * a part of the data access layer and gives access to the database data. 
 * It knows the database structure and implementation. 
 * <br/>
 * This class is implemented to access a PostgreSQL database implementation.
 * <br/>
 * Responsibilities:
 * <li>
 * 	<ul> Provide statements for inserting new product groups into the database. </ul>
 *  <ul> Provide statements for updating product groups into the database. </ul>
 *  <ul> Provide statements for deleting product groups from the database. </ul>
 *  <ul> Provide the database queries needed for product groups operations.  </ul>
 * </li>
 */

public class ProductGroupDAO {
	
	/**
	 * Default constructor of the ProductDAO class.
	 */
	public ProductGroupDAO() {
	}
	
	/**
	 * Query the database to get all the available product groups. 
	 * @throws Exception, in case any error take place during
	 * the query operation.
	 * @return ArrayList, list of all the product groups.
	 */
	public List getAllProductGroups() throws Exception {
		List result = new ArrayList();
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
		
        try {
        	// Load the objects
        	result = session.createQuery(
            "from ProductGroupOT g")
            .list();

            System.out.println( "Consulta realizada! " +
            		result == null || result.size() <= 0?(" No se ha encontrado ningún grupo!"):
            		("Se han encontrado: " + result.size() + " grupos"));
        }
        catch (HibernateException ex) {
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCTGROUP_NOT_FOUND, ex);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query (by primary key) the database to get a product group data. 
	 * @param idGroup long, Internal id of the product group.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ProductGroupOT, product group if it exists, null in any other case.
	 */
	public ProductGroupOT getProductGroup(long idGroup) throws ExplicaException {
		ProductGroupOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Load the object by the primary key (idResource)
        	result = (ProductGroupOT)session.get(ProductGroupOT.class, new Long(idGroup));

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún producto correspondiente!"):
            		("Se ha encontrado el producto: " + result.getDescription()));
        }
        catch (HibernateException ex) {
        	ArrayList lParams = new ArrayList();
        	lParams.add("" + idGroup);
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCTGROUP_NOT_FOUND, ex, lParams);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query the database to get a product group data. 
	 * @param description String, product group description.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ProductGroupOT, product group if it exists, null in any other case.
	 */
	public ProductGroupOT getProductGroup(String description) throws ExplicaException {
		ProductGroupOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Build the query statement
        	String sql = "FROM ProductGroupOT g WHERE g.description=?";
        	Query q = session.createQuery(sql);
        	q.setString(0, description);

        	// Run the query statement
        	result = (ProductGroupOT)q.uniqueResult();

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún grupo correspondiente!"):
            		("Se ha encontrado el grupo: " + result.getDescription()));
        }
        catch (HibernateException ex) {
        	ArrayList lParams = new ArrayList();
        	lParams.add("" + description);
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCTGROUP_NOT_FOUND, ex, lParams);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query the database to get a list of product groups that math
	 * the query criteria specified. 
	 * @param lCriteria ArrayList<CriteriaBean>, list of query criteria.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ArrayList<ProductGroupOT>, list of product groups matching the 
	 * searching criteria.
	 */
	public ArrayList<ProductGroupOT> getProductGroups(ArrayList<CriteriaBean> lCriteria) throws ExplicaException {
		ArrayList<ProductGroupOT> result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Build the query statement
        	SQLGenerator sqlGen = new SQLGenerator();
        	Criteria crit = sqlGen.getSQLCriteria(session, ProductGroupOT.class, lCriteria);
        	
        	// Run the query statement
        	result = (ArrayList)crit.list();

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún grupo correspondiente!"):
            		("Se han encontrado grupos: " + result.size()));
        }
        catch (HibernateException ex) {
/*        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCTGROUP_NOT_FOUND, ex, null);
        	throw new ExplicaException(msg);
*/
        	ex.printStackTrace();
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	public OperationResult delProductGroup(Long idGroup) throws ExplicaException {
		OperationResult result = new OperationResult();
		
		if (idGroup == null) {
			result.addResultMessage(OperationResult.RESULT_ERROR, 
					ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCTGROUP_NULL));
		}
		else {
			// Open a new Hibernate session to query the Database
			Session session = HibernateUtil.getSessionFactory().openSession();
	
			// Build the list of parameters for the localized operation result message.
			ArrayList lParams = new ArrayList();
	    	lParams.add(idGroup);
    	
	    	// Open a new transaction
	        Transaction tx = session.beginTransaction();
	
	        // Delete the product from the database
	        try {
	        	ProductGroupOT group = (ProductGroupOT)session.load(ProductGroupOT.class, idGroup);
	        	System.out.println("Product Group (" + group.getIdGroup() + ") found: " + group.getDescription());
	        	
	        	session.delete(group);
	        	tx.commit();
	        	
	        	System.out.println("Product Group (" + group.getIdGroup() + ") deleted: " + group.getDescription());
	        	result.addResultMessage(OperationResult.RESULT_OK, 
	        			ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.MSG_WAREHOUSE_DELETE_PRODUCTGROUP_OK, lParams));
	        }
	        catch (HibernateException ex) {
	        	ex.printStackTrace();
	        	tx.rollback();
	        	
	        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCTGROUP, ex, lParams);
	        	throw new ExplicaException(msg);
	        }
	        
	        // Close the Hibernate session
	        session.close();
	        
	        System.out.println("Session closed!");
		}
        
        return result;
	}
	
	public OperationResult delProductGroups(ArrayList<Long> lSelGroups) throws ExplicaException {
		OperationResult result = new OperationResult();
		
        try {
        	// Build the query statement
        	for (int i=0; lSelGroups != null && i<lSelGroups.size(); i++) {
        		// Delete the group
        		result.add(this.delProductGroup(lSelGroups.get(i)));
        	} // for each group
        }
        catch (HibernateException ex) {
        	ex.printStackTrace();
        	
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCTGROUP, ex, null);
        	throw new ExplicaException(msg);
        }
        
        return result;
	}
	
	public OperationResult saveProductGroup(ProductGroupOT group) throws ExplicaException {
		OperationResult result = new OperationResult();

		if (group == null) {
			result.addResultMessage(OperationResult.RESULT_ERROR, 
					ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_SAVE_PRODUCTGROUP_NULL));
		}
		else {
			// Build the list of parameters for the localized operation result message.
			ArrayList lParams = new ArrayList();
			lParams.add(group.getDescription());
		
			// Open a new Hibernate session to query the Database
			Session session = HibernateUtil.getSessionFactory().openSession();
	
	    	// Open a new transaction
	        Transaction tx = session.beginTransaction();
	
	        // Delete the product from the database
	        try {
	        	session.saveOrUpdate(group);
	        	tx.commit();
	        	
	        	System.out.println("Product (" + group.getIdGroup() + ") saved: " + group.getDescription());
	        	result.addResultMessage(OperationResult.RESULT_OK, 
	        			ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.MSG_WAREHOUSE_SAVE_PRODUCTGROUP_OK, lParams));
	        }
	        catch (HibernateException ex) {
	        	ex.printStackTrace();
	        	tx.rollback();
	        	
	        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_SAVE_PRODUCTGROUP, ex, lParams);
	        	throw new ExplicaException(msg);
	        }
	        
	        // Close the Hibernate session
	        session.close();
	        
	        System.out.println("Session closed!");
		} // else product!=null
        
        return result;
	}
	
	public long getNextProductGroupId() throws ExplicaException{
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

    	// Open a new transaction
        Transaction tx = session.beginTransaction();

        // Query the database for the next value from the productgroup_id_sequence
        try {
        	BigInteger id = (BigInteger)session.createSQLQuery(String.format("SELECT nextval ('%s')", InterfaceWarehouse.PRODUCTGROUP_ID_SEQUENCE_NAME)).
        		uniqueResult();
        	
        	tx.commit();
        	
        	System.out.println("Next product group id value got: " + id);
        	return id==null?-1:id.longValue();
        }
        catch (HibernateException ex) {
        	ex.printStackTrace();
        	tx.rollback();
        	
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_NEXT_PRODUCTGROUP, ex);
        	throw new ExplicaException(msg);
        }
        finally {
            // Close the Hibernate session
            session.close();
            
            System.out.println("Session closed!");	
        }
	}
	
	public void resetNextProductGroupId(long idGroup) throws ExplicaException{
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

    	// Open a new transaction
        Transaction tx = session.beginTransaction();

        // Update the database dec the next value from the productgroup_id_sequence
        try {
        	session.createSQLQuery(String.format("SELECT setval ('%s', %d)", InterfaceWarehouse.PRODUCTGROUP_ID_SEQUENCE_NAME, idGroup)).executeUpdate();
        	tx.commit();
        }
        catch (HibernateException ex) {
        	ex.printStackTrace();
        	tx.rollback();
        	
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_NEXT_PRODUCTGROUP, ex);
        	throw new ExplicaException(msg);
        }
        finally {
            // Close the Hibernate session
            session.close();
            
            System.out.println("Session closed!");	
        }
	}
	
	/**
	 * Returns tag Id assigned to CVS source file.
	 */
	public static String getRevision() {
		return "$Id: ProductGroupDAO.java,v 1.2 2009-03-02 15:31:23 mario Exp $";
	}
}

